/* 商品控制层 
code:状态码  message:操作消息  data:返回的数据(结果)
{code:xxx，message:xxx,data:xxx}
200 成功状态码
101 数据库执行失败
*/
const model=require('../model/model');

//获取所有书籍信息
exports.showBooksList=(req,res)=>{
    let sql =`SELECT * FROM book;`
    model.dbsql(sql,[],(err,results)=>{
        if(err){
            return res.send({code:101,message:'出现错误'+err.message,data:null})
        }
        res.send({code:200,message:null,data:results})
    })
}

// 搜索书籍
exports.searchBooks = (req, res) => {
    const { keyword } = req.query;
    
    if (!keyword) {
        return res.send({code:400, message:'搜索关键词不能为空', data:null});
    }
    
    // 使用LIKE进行模糊搜索，搜索书名、作者和出版社
    let sql = `SELECT * FROM book WHERE 
               bookname LIKE ? OR 
               author LIKE ? OR 
               publish LIKE ?`;
               
    const searchParam = `%${keyword}%`;
    
    model.dbsql(sql, [searchParam, searchParam, searchParam], (err, results) => {
        if(err) {
            return res.send({code:101, message:'搜索出错: '+err.message, data:null});
        }
        res.send({code:200, message:null, data:results});
    });
}

// showSubject
exports.showSubject = (req, res) => {
    let sql = `SELECT * FROM book_subject;`
    model.dbsql(sql, [], (err, results) => {
        if (err) {
            return res.send({ code: 101, message: '出现错误' + err.message, data: null })
        }
        
        // 处理完成的主题数量
        let completedSubjects = 0;
        
        // 循环处理每个主题，获取其分类
        results.forEach(subject => {
            let categorySql = `SELECT * FROM category WHERE sub_id = ?;`
            model.dbsql(categorySql, [subject.sub_id], (err, categories) => {
                if (err) {
                    return res.send({ code: 101, message: '获取分类出错' + err.message, data: null })
                }
                
                // 将分类数据添加到主题对象
                subject.categories = categories;
                
                // 增加已完成的主题计数
                completedSubjects++;
                
                // 如果所有主题都处理完毕，返回结果
                if (completedSubjects === results.length) {
                    res.send({ code: 200, message: null, data: results })
                }
            });
        });
    })
}

// 获取主题分类
exports.showCategory=(req,res)=>{
    let sql=`SELECT * FROM category;`
    model.dbsql(sql,[],(err,results)=>{
        if(err){
            return res.send({code:101,message:'出现错误'+err.message,data:null})
    }
    res.send({code:200,message:null,data:results})
    })
}

// 根据分类ID获取书籍
exports.getBooksByCategory = (req, res) => {
    const { category_id } = req.params;
    
    console.log("Requested category_id:", category_id);
    
    if (!category_id) {
        return res.send({ code: 400, message: '缺少分类ID', data: null });
    }
    
    // Debug: Get all books to see what's in the database
    let debugSql = `SELECT * FROM book LIMIT 10;`
    model.dbsql(debugSql, [], (err, debugResults) => {
        if (err) {
            console.error("Debug query error:", err);
        } else {
            console.log("Sample books:", debugResults);
        }
        
        // Now run the actual query
        let sql = `SELECT * FROM book WHERE c_id = ?;`
        model.dbsql(sql, [category_id], (err, results) => {
            if (err) {
                console.error("Error in getBooksByCategory:", err);
                return res.send({ code: 101, message: '出现错误' + err.message, data: null });
            }
            
            console.log(`Books found for category ${category_id}:`, results.length);
            
            // If no results, try alternative query (in case of string/number type mismatch)
            if (results.length === 0) {
                console.log("No results found, trying alternative query");
                let altSql = `SELECT * FROM book WHERE c_id = ? OR c_id = ?;`
                model.dbsql(altSql, [category_id, parseInt(category_id)], (err, altResults) => {
                    if (err) {
                        console.error("Alternative query error:", err);
                        return res.send({ code: 101, message: '出现错误' + err.message, data: null });
                    }
                    
                    console.log(`Books found with alternative query:`, altResults.length);
                    res.send({ code: 200, message: null, data: altResults });
                });
            } else {
                res.send({ code: 200, message: null, data: results });
            }
        });
    });
}

// 获取书籍详情（包含对应的评论）
exports.showBooksDetails=(req,res)=>{
    let {id} =req.params
    let sql =`SELECT * FROM book WHERE b_id=?;
SELECT comments.*,username FROM comments JOIN users on comments.u_id=users.u_id WHERE b_id=?;`
    model.dbsql(sql,[id,id],(err,results)=>{
        if(err){
                    return res.send({code:101,message:'出现错误'+err.message,data:null})
        }
         res.send({code:200,message:null,data:results})
    })
}


// 展示首页推荐图书（分组展示）
exports.showHotbooks = (req, res) => {
    const sql = `
      SELECT h.sec_id, s.sec_name AS sort, b.*
      FROM hotbook h
      JOIN book b ON h.b_id = b.b_id
      JOIN section s ON h.sec_id = s.sec_id
      ORDER BY h.sec_id, h.hotbook_id;
    `;
  
    model.dbsql(sql, [], (err, results) => {
      if (err) {
        return res.send({ code: 101, message: '出现错误: ' + err.message, data: null });
      }
  
      const grouped = {};
      results.forEach(row => {
        if (!grouped[row.sort]) grouped[row.sort] = [];
  
        grouped[row.sort].push({
          id: row.b_id,
          name: row.bookname,
          price: row.price,
          coverImg: row.img,
          font: `${row.author} - ${row.publish}`
        });
      });
  
      const data = Object.keys(grouped).map(key => ({
        sort: key,
        book: grouped[key]
      }));
  
      res.send({ code: 200, message: null, data });
    });
  };
  
exports.getBooksSubject = (req, res) => {
    const sqlSubject = `SELECT * FROM book_subject`;
  
    model.dbsql(sqlSubject, [], (err, subjects) => {
      if (err) {
        return res.send({ code: 101, message: '数据库错误: ' + err.message, data: null });
      }
  
      const results = [];
  
      let count = 0;
  
      subjects.forEach(subject => {
        const sqlCategory = `
          SELECT * FROM category WHERE sub_id = ?
        `;
  
        model.dbsql(sqlCategory, [subject.sub_id], (err, categories) => {
          if (err) {
            return res.send({ code: 101, message: '数据库错误: ' + err.message, data: null });
          }
  
          const catIds = categories.map(cat => cat.cat_id);
  
          if (catIds.length === 0) {
            results.push({
              ...subject,
              subject: []
            });
  
            count++;
            if (count === subjects.length) {
              return res.send({ code: 200, message: null, data: results });
            }
            return;
          }
  
          const sqlBooks = `
            SELECT b_id AS id, bookname AS name, price, img, author, publish
            FROM book
            WHERE cat_id IN (${catIds.join(',')})
          `;
  
          model.dbsql(sqlBooks, [], (err, books) => {
            if (err) {
              return res.send({ code: 101, message: '数据库错误: ' + err.message, data: null });
            }
  
            const subjectData = {
              ...subject,
              subject: [
                {
                  title: `${subject.sub_name} 精选推荐`,
                  recom: `这些${subject.sub_name}类图书广受好评，值得一读。`,
                  books: books || []
                }
              ]
            };
  
            results.push(subjectData);
            count++;
  
            if (count === subjects.length) {
              return res.send({ code: 200, message: null, data: results });
            }
          });
        });
      });
    });
  };

// 主编推荐接口
exports.showEditorRecommend = (req, res) => {
  // 只查"主编推荐"5本书
  const sql = `
    SELECT b_id, bookname, img, price, intro
    FROM book
    WHERE sort='主编推荐'
    ORDER BY b_id
    LIMIT 5
  `;
  model.dbsql(sql, [], (err, results) => {
    if (err) {
      return res.send({ code: 101, message: '出现错误: ' + err.message, data: null });
    }
    res.send({ code: 200, message: null, data: results });
  });
};

// 查询所有栏目（section）
exports.getSections = (req, res) => {
  const sql = `SELECT sec_id, sec_name FROM section ORDER BY sec_id`;
  model.dbsql(sql, [], (err, results) => {
    if (err) {
      return res.send({ code: 101, message: '出现错误: ' + err.message, data: null });
    }
    res.send({ code: 200, message: null, data: results });
  });
};

// 展示某个栏目下的推荐图书
exports.showHotbooksBySection = (req, res) => {
  const secName = req.query.section;
  const sql = `
    SELECT h.sec_id, s.sec_name AS sort, b.*
    FROM hotbook h
    JOIN book b ON h.b_id = b.b_id
    JOIN section s ON h.sec_id = s.sec_id
    WHERE s.sec_name = ?
    ORDER BY h.hotbook_id
    LIMIT 5
  `;
  model.dbsql(sql, [secName], (err, results) => {
    if (err) {
      return res.send({ code: 101, message: '出现错误: ' + err.message, data: null });
    }
    // 字段适配
    const books = results.map(row => ({
      id: row.b_id,
      name: row.bookname,
      coverImg: row.img,
      price: row.price,
      font: row.introduce || row.intro // 优先用hotbook.introduce
    }));
    res.send({ code: 200, message: null, data: books });
  });
};

// 获取随机书籍
exports.getRandomBooks = (req, res) => {
    // 获取请求参数中的数量，默认为4
    const count = req.query.count ? parseInt(req.query.count) : 4;
    
    // 使用ORDER BY RAND()随机排序并限制结果数量
    let sql = `SELECT * FROM book ORDER BY RAND() LIMIT ?`;
    
    model.dbsql(sql, [count], (err, results) => {
        if(err) {
            return res.send({code:101, message:'获取随机书籍出错: '+err.message, data:null});
        }
        res.send({code:200, message:null, data:results});
    });
};

// 管理员获取图书列表
exports.getAdminBooksList = (req, res) => {
  // 获取查询参数
  let { page = 1, limit = 10, title = '' } = req.query;
  page = parseInt(page);
  limit = parseInt(limit);
  const offset = (page - 1) * limit;

  // 构建WHERE条件
  let conditions = [];
  let params = [];

  if (title) {
    conditions.push('bookname LIKE ?');
    params.push(`%${title}%`);
  }

  const whereClause = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';

  // 计算总数的SQL
  const countSql = `SELECT COUNT(*) as total FROM book ${whereClause}`;

  // 查询数据的SQL - 根据实际表结构调整
  const querySql = `
    SELECT 
      b_id as id, 
      bookname as title, 
      author, 
      publish,
      img as cover,
      price,
      stock,
      intro
    FROM book
    ${whereClause} 
    ORDER BY b_id DESC 
    LIMIT ?, ?
  `;
  
  // 添加分页参数
  params.push(offset, limit);

  // 执行查询
  model.dbsql(countSql, params.slice(0, params.length - 2), (err, countResult) => {
    if (err) {
      return res.send({ code: 101, message: '查询出错: ' + err.message, data: null });
    }

    const total = countResult[0].total;
    
    // 如果没有数据，直接返回空数组
    if (total === 0) {
      return res.send({ 
        code: 200, 
        message: '获取成功', 
        data: { 
          list: [], 
          total: 0, 
          page, 
          limit 
        } 
      });
    }

    // 查询图书数据
    model.dbsql(querySql, params, (err, results) => {
      if (err) {
        return res.send({ code: 101, message: '查询出错: ' + err.message, data: null });
      }

      // 处理结果，为每本书添加图片完整URL和默认状态
      const bookList = results.map(book => {
        // 如果cover不是完整URL，添加前缀
        if (book.cover && !book.cover.startsWith('http')) {
          book.cover = `http://localhost:3000/uploads/${book.cover}`;
        }
        
        // 由于book表中没有status字段，添加默认值
        book.status = 1; // 默认为上架状态
        
        return book;
      });

      console.log('返回给前端的图书列表数据:', JSON.stringify(bookList));

      res.send({ 
        code: 200, 
        message: '获取成功', 
        data: { 
          list: bookList, 
          total, 
          page, 
          limit 
        } 
      });
    });
  });
};

// 获取图书详情
exports.getAdminBookDetail = (req, res) => {
  const { id } = req.params;
  
  if (!id) {
    return res.send({ code: 400, message: '缺少图书ID', data: null });
  }
  
  const sql = `
    SELECT 
      b.b_id as id, 
      b.bookname as title, 
      b.author, 
      b.publish,
      b.img as cover,
      b.price,
      b.stock,
      b.intro
    FROM book as b
    WHERE b.b_id = ?
  `;
  
  model.dbsql(sql, [id], (err, results) => {
    if (err) {
      return res.send({ code: 101, message: '获取图书详情失败: ' + err.message, data: null });
    }
    
    if (results.length === 0) {
      return res.send({ code: 404, message: '图书不存在', data: null });
    }
    
    // 处理图片URL和添加状态字段
    const book = results[0];
    if (book.cover && !book.cover.startsWith('http')) {
      book.cover = `http://localhost:3000/uploads/${book.cover}`;
    }
    
    // 添加默认状态
    book.status = 1;
    
    res.send({ code: 200, message: '获取成功', data: book });
  });
};

// 更新图书状态
exports.updateBookStatus = (req, res) => {
    const { id, status } = req.body;
    
    if (!id) {
        return res.send({ code: 400, message: '缺少图书ID', data: null });
    }
    
    const sql = `UPDATE book SET status = ? WHERE b_id = ?`;
    
    model.dbsql(sql, [status, id], (err, result) => {
        if (err) {
            return res.send({ code: 101, message: '更新图书状态失败: ' + err.message, data: null });
        }
        
        res.send({ code: 200, message: '更新图书状态成功', data: null });
    });
};

// 更新图书信息
exports.updateBook = (req, res) => {
    const { id, title, author, publish, cover, price, inventory, stock, intro } = req.body;
    
    console.log('接收到的更新请求数据:', req.body);
    
    if (!id) {
        return res.send({ code: 400, message: '缺少图书ID', data: null });
    }
    
    // 确保数值字段不为null
    // 优先使用stock字段，如果不存在则使用inventory字段
    const safeStock = (stock !== null && stock !== undefined) ? stock : 
                     (inventory !== null && inventory !== undefined) ? inventory : 0;
    const safePrice = price !== null && price !== undefined ? price : 0;
    
    console.log('处理后的数据:', { title, author, publish, cover, price: safePrice, stock: safeStock, intro });
    
    const sql = `UPDATE book SET 
                bookname = ?, 
                author = ?, 
                publish = ?, 
                img = ?, 
                price = ?, 
                stock = ?, 
                intro = ? 
                WHERE b_id = ?`;
    
    model.dbsql(sql, [title, author, publish, cover, safePrice, safeStock, intro, id], (err, result) => {
        if (err) {
            return res.send({ code: 101, message: '更新图书失败: ' + err.message, data: null });
        }
        
        res.send({ code: 200, message: '更新图书成功', data: null });
    });
};

// 添加图书
exports.addBook = (req, res) => {
    const { title, author, publish, cover, price, inventory, stock, intro } = req.body;
    
    console.log('接收到的添加请求数据:', req.body);
    
    // 验证必填字段
    if (!title || !author || !price) {
        return res.send({ code: 400, message: '书名、作者和价格为必填项', data: null });
    }
    
    // 确保数值字段不为null
    // 优先使用stock字段，如果不存在则使用inventory字段
    const safeStock = (stock !== null && stock !== undefined) ? stock : 
                     (inventory !== null && inventory !== undefined) ? inventory : 0;
    const safePrice = price !== null && price !== undefined ? price : 0;
    
    console.log('处理后的数据:', { title, author, publish, cover, price: safePrice, stock: safeStock, intro });
    
    const sql = `INSERT INTO book 
                (bookname, author, publish, img, price, stock, intro, status, sub_id) 
                VALUES (?, ?, ?, ?, ?, ?, ?, 1, 1)`;
    
    model.dbsql(sql, [title, author, publish, cover, safePrice, safeStock, intro], (err, result) => {
        if (err) {
            return res.send({ code: 101, message: '添加图书失败: ' + err.message, data: null });
        }
        
        res.send({ code: 200, message: '添加图书成功', data: { id: result.insertId } });
    });
};

// 删除图书
exports.deleteBook = (req, res) => {
    const { id } = req.params;
    
    if (!id) {
        return res.send({ code: 400, message: '缺少图书ID', data: null });
    }
    
    const sql = `DELETE FROM book WHERE b_id = ?`;
    
    model.dbsql(sql, [id], (err, result) => {
        if (err) {
            return res.send({ code: 101, message: '删除图书失败: ' + err.message, data: null });
        }
        
        if (result.affectedRows === 0) {
            return res.send({ code: 404, message: '图书不存在', data: null });
        }
        
        res.send({ code: 200, message: '删除图书成功', data: null });
    });
};
